package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import entity.Notice;
import util.DBUtil;

public class NoticeDaoImpl implements NoticeDao {

	@Override
	public List<Notice> queryAll() {
		
		List<Notice> noticeList = new ArrayList();
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from notice order by public_date desc";
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()){
				Notice n = new Notice();
				n.setId( rs.getString("id")  );
				n.setTitle(  rs.getString("title")  );
				n.setAuthor(  rs.getString("author")  );
				n.setContent( rs.getString("content")  );
				n.setNotice_status( rs.getInt("notice_status") );
				n.setPublic_date( rs.getTimestamp("public_date") );
				noticeList.add(n);
			}
			
		} catch (Exception e) {
			System.out.println("查询数据库时获取异常:"+e.getMessage());
		}
		
		DBUtil.close(rs);
		DBUtil.close(pst);
		//关闭连接
		DBUtil.close(conn);
		return noticeList;
	}

	@Override
	public Notice queryById(String id) {
		Notice n = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from notice where id=?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, id);
			rs = pst.executeQuery();
			if(rs.next()){
				n = new Notice();
				n.setId( rs.getString("id")  );
				n.setTitle(  rs.getString("title")  );
				n.setAuthor(  rs.getString("author")  );
				n.setContent( rs.getString("content")  );
				n.setNotice_status( rs.getInt("notice_status") );
				n.setPublic_date( rs.getTimestamp("public_date") );
			}
			
		} catch (Exception e) {
			System.out.println("查询数据库时获取异常:"+e.getMessage());
		}
		
		DBUtil.close(rs);
		DBUtil.close(pst);
		//关闭连接
		DBUtil.close(conn);
		return n;
		
		
	}

	@Override
	public int updateNotice(Notice notice) {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "update notice set title=?,author=?,content=?,notice_status=?,public_date=? where id=?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, notice.getTitle());
			pst.setString(2, notice.getAuthor());
			pst.setString(3, notice.getContent());
			pst.setInt(4, notice.getNotice_status());
			pst.setTimestamp(5, new Timestamp( notice.getPublic_date().getTime() ));
			pst.setString(6, notice.getId());
			pst.execute();
			
			DBUtil.close(pst);
			//关闭连接
			DBUtil.close(conn);
			return 1;
			
		} catch (Exception e) {
			System.out.println("查询数据库时获取异常:"+e.getMessage());
			DBUtil.close(pst);
			//关闭连接
			DBUtil.close(conn);
			return 0;
		}
		
		
	}

	//删除数据
	@Override
	public boolean deleteById(String id) {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "delete from notice where id=?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, id);
			pst.execute();
			DBUtil.close(pst);
			//关闭连接
			DBUtil.close(conn);
			return true;
			
		} catch (Exception e) {
			System.out.println("查询数据库时获取异常:"+e.getMessage());
			//DBUtil.close(pst);
			//关闭连接
			//DBUtil.close(conn);
			return false;
			
		}
		
		
		
	}

	@Override
	public boolean deleteByIds(String[] id) {
		//把数组，变成('aa','bbb','tt')的字符串
		String sql_tmp = "(";
		for (String bb:id){
			sql_tmp = sql_tmp + "'";
			sql_tmp = sql_tmp + bb;
			sql_tmp = sql_tmp + "',";
		}
		sql_tmp = sql_tmp + ")";
		sql_tmp = sql_tmp.replace( ",)",")");
		
		
		
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "delete from notice where id in "+sql_tmp;
			pst = conn.prepareStatement(sql);
			//pst.setString(1, id);
			pst.execute();
			DBUtil.close(pst);
			//关闭连接
			DBUtil.close(conn);
			return true;
			
		} catch (Exception e) {
			System.out.println("查询数据库时获取异常:"+e.getMessage());
			//DBUtil.close(pst);
			//关闭连接
			//DBUtil.close(conn);
			return false;
			
		}
	}

	@Override
	public List<Notice> queryNoticeByContent(String content) {
		List<Notice> noticeList = new ArrayList();
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from notice where content like ? order by public_date desc";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+content+"%");
			rs = pst.executeQuery();
			while(rs.next()){
				Notice n = new Notice();
				n.setId( rs.getString("id")  );
				n.setTitle(  rs.getString("title")  );
				n.setAuthor(  rs.getString("author")  );
				n.setContent( rs.getString("content")  );
				n.setNotice_status( rs.getInt("notice_status") );
				n.setPublic_date( rs.getTimestamp("public_date") );
				noticeList.add(n);
			}
			
		} catch (Exception e) {
			System.out.println("查询数据库时获取异常:"+e.getMessage());
		}
		
		DBUtil.close(rs);
		DBUtil.close(pst);
		//关闭连接
		DBUtil.close(conn);
		return noticeList;
	}

}
